/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package JavaBeans;

import java.util.Vector;
import Servlet.dataBaseController;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import javax.swing.JOptionPane;

/**
 *
 * @author Brian
 */
public class Stock {
    
    private static Map stock_map = new HashMap();
    private static Map history_map = new HashMap();
    private String symbol;
    private String name;
    private String type;
    private int numShare;
    private float price;
    private String img;
    private float rate;
    
    public String getImg() { return img; }

    public void setImg(String img) {this.img = img; }

    public String getName() {return name; }

    public void setName(String name) {this.name = name; }

    public float getPrice() { return price; }

    public void setPrice(float price) { this.price = price; }

    public String getSymbol() { return symbol; }

    public void setSymbol(String symbol) { this.symbol = symbol; }

    public String getType() { return type; }

    public void setType(String type) { this.type = type; }

    public int getNumShare() { return numShare; }

    public void setNumShare(int numShare) { this.numShare = numShare; }
    
    public float getRate() { return rate; }
    
    public void setRate(float rate) { this.rate = rate; }
    
    

    public static Vector getStockList() throws SQLException {
        updateNumShare_and_history();
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        String strQuery = null;
        Vector<Stock> StockList = new Vector<Stock>();
        try {
            connection = Servlet.dataBaseController.getConnection();
        } catch (Exception e) {
            System.out.println("Error:");
        }
        
        
        try {
            st = connection.createStatement();
            strQuery = "select S.StockSymbol, S.CompanyName, S.Type, S.PricePerShare, S.img from `brchung`.`stock` S ORDER by PricePerShare DESC";
            rs = st.executeQuery(strQuery);
            while (rs.next()) {
                Stock stock = new Stock();
                stock.setName(rs.getString("CompanyName"));
                stock.setType(rs.getString("Type"));
                stock.setSymbol(rs.getString("StockSymbol"));;
                stock.setImg(rs.getString("img"));
                String price = rs.getString("PricePerShare");
                float p = Float.valueOf(price.trim()).floatValue();
                stock.setPrice(p); 
                
                int share = 0;
                if(stock_map.containsKey(stock.getSymbol()))
                    share = stock_map.get(stock.getSymbol()).hashCode();
                stock.setNumShare(share);
                
                float old_share = 0;
                float old;
                if(history_map.containsKey(stock.getSymbol())){
                    old = Float.valueOf(history_map.get(stock.getSymbol()).toString().trim()).floatValue();
                    old_share = (p-old)/old;
                }
                stock.setRate(old_share);
                
                StockList.add(stock);
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }
        
        
        return StockList;
    }
    
    public static void updateNumShare_and_history() throws SQLException {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        String strQuery = null;
        
        try {
            connection = Servlet.dataBaseController.getConnection();
        } catch (Exception e) {
            System.out.println("Error:");
        }
        try {
            st = connection.createStatement();
            strQuery = "select stockid, sum(numshares) "
                    + "from brchung.trade t, brchung.order1 o"
                    + " where t.orderid = o.id and order1 = 'buy' group by stockid";
            rs = st.executeQuery(strQuery);
            while (rs.next()) {
                String stockID = rs.getString("stockid");
                int share = Integer.parseInt(rs.getString("sum(numshares)"));
                stock_map.put(stockID, share);
            }
            
            st = connection.createStatement();
            strQuery = "select stockid, sum(numshares) "
                    + "from brchung.trade t, brchung.order1 o"
                    + " where t.orderid = o.id and order1 = 'sell' group by stockid";
            rs = st.executeQuery(strQuery);
            while (rs.next()) {
                String stockID = rs.getString("StockID");
                int sell = Integer.parseInt(rs.getString("sum(numshares)"));
                int share = stock_map.get(stockID).hashCode() - sell;
                stock_map.put(stockID, share);
            }
            
            
            st = connection.createStatement();
            strQuery = "select * "+
                       "from (select * from brchung.stockhistory order by date DESC) s1, "+
                                              "(select max(date) as date, stockid from brchung.stockhistory group by stockid) s2 "+
                                              "where s1.date < s2.date and s1.stockid = s2.stockid group by s1.stockid";
            rs = st.executeQuery(strQuery);
            while (rs.next()) {
                String stockID = rs.getString("stockid");
                String old_share = rs.getString("pricepershare");
                history_map.put(stockID, old_share);
            }
            
            
            
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }
}
